<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><head><title>Server Prepared Statements</title><meta name="generator" content="DocBook XSL Stylesheets V1.76.1"/><link rel="home" href="index.html" title="The PostgreSQL™ JDBC Interface"/><link rel="up" href="ext.html" title="Chapter 9. PostgreSQL™ Extensions to the JDBC API"/><link rel="prev" href="listennotify.html" title="Listen / Notify"/><link rel="next" href="thread.html" title="Chapter 10. Using the Driver in a Multithreaded or a Servlet Environment"/></head><body><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Server Prepared Statements</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="listennotify.html">Prev</a> </td><th width="60%" align="center">Chapter 9. <span class="productname">PostgreSQL</span>™ Extensions to the
    <acronym class="acronym">JDBC</acronym> <acronym class="acronym">API</acronym></th><td width="20%" align="right"> <a accesskey="n" href="thread.html">Next</a></td></tr></table><hr/></div><div class="sect1" title="Server Prepared Statements"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="server-prepare"/>Server Prepared Statements</h2></div></div></div><p>
    The <span class="productname">PostgreSQL</span>™ server allows clients
    to compile sql statements that are expected to be reused to avoid the
    overhead of parsing and planning the statement for every execution. 
    This functionality is available at the <acronym class="acronym">SQL</acronym> level
    via PREPARE and EXECUTE beginning with server version 7.3, and at the
    protocol level beginning with server version 7.4, but as Java
    developers we really just want to use the standard
    <code class="classname">PreparedStatement</code> interface.
   </p><div class="note" title="Note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
     Previous versions of the driver used PREPARE and EXECUTE to
     implement server-prepared statements.  This is supported on all server
     versions beginning with 7.3, but produced application-visible changes
     in query results, such as missing ResultSet metadata and row update
     counts. The current driver uses the V3 protocol-level equivalents which
     avoid these changes in query results, but the V3 protocol is only
     available beginning with server version 7.4.  Enabling server-prepared
     statements will have no affect when connected to a 7.3 server or when
     explicitly using the V2 protocol to connect to a 7.4 server.
    </p></div><p>
    There are a number of ways to enable server side prepared statements
    depending on your application's needs.  The general method is to
    set a threshold for a <code class="classname">PreparedStatement</code>.
    An internal counter keeps track of how many times the statement has
    been executed and when it reaches the threshold it will start to
    use server side prepared statements.
   </p><div class="note" title="Note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
     Server side prepared statements are planned only once by the server.
     This avoids the cost of replanning the query every time, but also
     means that the planner cannot take advantage of the particular
     parameter values used in a particular execution of the query.
     You should be cautious about enabling the use of server side prepared
     statements globally.
    </p></div><div class="example"><a id="server-prepared-statement-example"/><p class="title"><strong>Example 9.3. Using server side prepared statements</strong></p><div class="example-contents"><pre class="programlisting">
import java.sql.*;

public class ServerSidePreparedStatement
{

	public static void main(String args[]) throws Exception
	{
		Class.forName("org.postgresql.Driver");
		String url = "jdbc:postgresql://localhost:5432/test";
		Connection conn = DriverManager.getConnection(url,"test","");

		PreparedStatement pstmt = conn.prepareStatement("SELECT ?");

		// cast to the pg extension interface
		org.postgresql.PGStatement pgstmt = (org.postgresql.PGStatement)pstmt;

		// on the third execution start using server side statements
		pgstmt.setPrepareThreshold(3);

		for (int i=1; i&lt;=5; i++)
		{
			pstmt.setInt(1,i);
			boolean usingServerPrepare = pgstmt.isUseServerPrepare();
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			System.out.println("Execution: "+i+", Used server side: " + usingServerPrepare + ", Result: "+rs.getInt(1));
			rs.close();
		}

		pstmt.close();
		conn.close();
	}
}
</pre><p>Which produces the expected result of using server side prepared statements upon the third execution.</p><pre class="programlisting">
Execution: 1, Used server side: false, Result: 1
Execution: 2, Used server side: false, Result: 2
Execution: 3, Used server side: true, Result: 3
Execution: 4, Used server side: true, Result: 4
Execution: 5, Used server side: true, Result: 5
</pre></div></div><br class="example-break"/><p>
    The example shown above requires the programmer to use
    <span class="productname">PostgreSQL</span>™ specific code in a
    supposedly portable API which is not ideal.  Also it sets the
    threshold only for that particular statement which is some extra
    typing if we wanted to use that threshold for every statement.
    Let's take a look at the other ways to set the threshold to enable
    server side prepared statements.  There is already a hierarchy in
    place above a <code class="classname">PreparedStatement</code>, the
    <code class="classname">Connection</code> it was created from, and above that
    the source of the connection be it a <code class="classname">Datasource</code>
    or a <acronym class="acronym">URL</acronym>.  The server side prepared statement
    threshold can be set at any of these levels such that the value
    will be the default for all of it's children.
   </p><pre class="programlisting">
// pg extension interfaces
org.postgresql.PGConnection pgconn;
org.postgresql.PGStatement pgstmt;

// set a prepared statement threshold for connections created from this url
String url = "jdbc:postgresql://localhost:5432/test?prepareThreshold=3";

// see that the connection has picked up the correct threshold from the url
Connection conn = DriverManager.getConnection(url,"test","");
pgconn = (org.postgresql.PGConnection)conn;
System.out.println(pgconn.getPrepareThreshold()); // Should be 3

// see that the statement has picked up the correct threshold from the connection
PreparedStatement pstmt = conn.prepareStatement("SELECT ?");
pgstmt = (org.postgresql.PGStatement)pstmt;
System.out.println(pgstmt.getPrepareThreshold()); // Should be 3

// change the connection's threshold and ensure that new statements pick it up
pgconn.setPrepareThreshold(5);
PreparedStatement pstmt = conn.prepareStatement("SELECT ?");
pgstmt = (org.postgresql.PGStatement)pstmt;
System.out.println(pgstmt.getPrepareThreshold()); // Should be 5
</pre></div><div class="navfooter"><hr/><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="listennotify.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ext.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="thread.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Listen / Notify </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 10. Using the Driver in a Multithreaded or a Servlet Environment</td></tr></table></div></body></html>